#!/usr/bin/perl -w
use strict;

use FindBin;
use lib ("$FindBin::Bin/..", "/net/cpp-group/Leo/bin");
use DBI;
use DBD::Pg;

use ensembl_parameters;
use run_db_cmd;
use run_cmd;
print STDERR <<"HEADLINE";
888888888888888888888888888888888888888888888888888888888888888888888

    create_db_schema

	Description:
		Creates PostgreSQL tables for the entire genome project

888888888888888888888888888888888888888888888888888888888888888888888

HEADLINE



# Connect to panda
my $dbh = connect_to_panda();
$dbh->{RaiseError} = 1;
$dbh->{PrintError} = 0;
#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888

#       Create schemata

#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888
sub create_table($$$$)
{
	my ($dbh, $table, $fields, $description) = @_;
	eval
	{
		$dbh->do("CREATE TABLE $table($fields);");
	};
	if ($@)
	{
		if ($dbh->errstr !~ /already exists/)
		{
			die $dbh->errstr. "$@";
		}
	}
	$dbh->do(<<"PL/SQLCMD") or die;
	GRANT SELECT ON TABLE $table TO GROUP select_panda;
	TRUNCATE $table;
    COMMENT ON TABLE $table IS '$description';
PL/SQLCMD
}

sub create_schema($$)
{
	my ($dbh, $schema) = @_;
	eval
	{
		$dbh->do(<<"PL/SQLCMD");
		CREATE SCHEMA $schema;
PL/SQLCMD
	};
	if ($@)
	{
		if ($dbh->errstr !~ /already exists/)
		{
			die $dbh->errstr. "$@";
		}
	}
	$dbh->do(<<"PL/SQLCMD") or die;;
		GRANT usage ON SCHEMA $schema TO GROUP select_panda;
PL/SQLCMD
}


$dbh->do("SET client_min_messages=warning;") or die;;
print STDERR "Create schemas...\n";
create_schema($dbh, $_) for (qw(genomes orthologs domains taxon));

#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888

#       Create genome table

#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888
print STDERR "Create genome tables...\n";
create_table($dbh, "genomes.ensembl_parameters", <<"PL/SQLCMD", "Ensembl versions and taxon data");
    species             TEXT,
    ens_taxon           TEXT,
    ens_version         TEXT,
    ens_mart            TEXT,
    ens_commonname      TEXT,
    ens_taxon_abbrev    TEXT
PL/SQLCMD

create_table($dbh, "genomes.taxa_parameters", <<"PL/SQLCMD", "Per taxon identifier and data");
    species             TEXT,
    panda_db_origin     TEXT,
    tax_id              INTEGER,
    id_prefix           TEXT,
    sequence_directory  TEXT
PL/SQLCMD


create_table($dbh, "genomes.analysis_log", <<"PL/SQLCMD", "Start and end times for each stage in phyop");
    protocol_id  INTEGER,
    analysis_id  INTEGER,
    description  TEXT,
    start_change timestamp,
    end_change   timestamp
PL/SQLCMD


#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888

#       Load ensemb parameters

#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888
sub insert_ens_taxon_parameters_into_db(\@\%)
{
	my ($db_data, $ens_taxon_to_version) = @_;
	# place previously parsed record into panda
	if (@$db_data)
	{
		die "Error\n\tEach species must be specified by 6 entries ".
					"in the ENSEMBL_DB_SOURCES file (See '$db_data->[0]')."
			unless @$db_data == 6;
		$dbh->do("INSERT INTO genomes.ensembl_parameters VALUES (".
							join(',',('?') x 6).");", undef, @$db_data) or die;;

		$ens_taxon_to_version->{$db_data->[0]} = $db_data->[2];
	}
}
sub insert_taxon_parameters_into_db(\@\%)
{
	my ($db_data, $ens_taxon_to_version) = @_;
	# place previously parsed record into panda
	if (@$db_data)
	{
		die "Error\n\tEach species must be specified by 5 entries ".
					"in the TAXA_PARAMETERS file (See '$db_data->[0]')."
			unless @$db_data == 5;

		#
		#	override directory if ensembl
		#
		my $species = $db_data->[0];
		if (exists $ens_taxon_to_version->{$species})
		{
			my $ens_ver = $ens_taxon_to_version->{$species};
			#
			#	be careful here not to introduce circular dependencies
			#
			use db_parameters;
			$db_data->[SEQ_DIR] ="$dir_sequence_root/$species"."_$ens_ver";
		}
		$dbh->do("INSERT INTO genomes.taxa_parameters VALUES (".
							join(',',('?') x 5).");", undef, @$db_data) or die;
	}
}

#
#	save ensembl versions to load in ensembl directories automatically
#
my %ens_taxon_to_version;
{
	# Use the file ENSEMBL_DB_SOURCES
	# or else that specified in the environment variable GENOME_DB_PARAM_DIR
	my $file_name = "$FindBin::Bin/../ENSEMBL_DB_SOURCES";
	if ($ENV{GENOME_DB_PARAM_DIR})
	{
		$file_name = $ENV{GENOME_DB_PARAM_DIR};
		$file_name =~ s/^(.*)\/?$/$1\/ENSEMBL_DB_SOURCES/;
	}

	open DB_SRC, $file_name or
		die "Error\n\tCould not open $file_name$@";


	my @db_data;
    for (<DB_SRC>)
    {
		# ignore blank lines or comments
        chomp;
        next if /^\s*$/;
		next if /^#/;

        # start of new record
        if (/^>(.*)/)
        {
			insert_ens_taxon_parameters_into_db(@db_data, %ens_taxon_to_version);
			@db_data = ($1);
            next;
        }

        # save field
        push(@db_data, $_);
    }

	# insert last record
	insert_ens_taxon_parameters_into_db(@db_data, %ens_taxon_to_version);

}


{
	# Use the file TAXA_PARAMETERS
	# or else that specified in the environment variable GENOME_DB_PARAM_DIR
	my $file_name = "$FindBin::Bin/../TAXA_PARAMETERS";
	if ($ENV{GENOME_DB_PARAM_DIR})
	{
		$file_name = $ENV{GENOME_DB_PARAM_DIR};
		$file_name =~ s/^(.*)\/?$/$1\/TAXA_PARAMETERS/;
	}

	open DB_SRC, $file_name or
		die "Error\n\tCould not open $file_name$@";


	my @db_data;
    for (<DB_SRC>)
    {
		# ignore blank lines or comments
        chomp;
        next if /^\s*$/;
		next if /^#/;

        # start of new record
        if (/^>(.*)/)
        {
			insert_taxon_parameters_into_db(@db_data, %ens_taxon_to_version);
			@db_data = ($1);
            next;
        }

        # save field
        push(@db_data, $_);
    }

	# insert last record
	insert_taxon_parameters_into_db(@db_data, %ens_taxon_to_version);

}










#_________________________________________________________________________________________

#	Prepare data for entering into postgres


#_________________________________________________________________________________________
print STDERR "\n";

print STDERR "\tCreate plpgsql language for postgresql macros...\n";
run_cmd(<<"CMD");
	createlang --host $db_host --user $db_user -d $db_name 'plpgsql' 
CMD

print STDERR "\tPrepare tables for postgres...\n";
run_cmd(<<"CMD");
	$FindBin::Bin/prepare_create_sql.pl
		> $FindBin::Bin/create.sql
CMD


#_________________________________________________________________________________________

#	Entering into postgres

#_________________________________________________________________________________________
print STDERR "\n";
print STDERR "\tEntering tables into postgres...\n";
run_psql_file("$FindBin::Bin/create.sql", $FindBin::Bin);

#_________________________________________________________________________________________

#	Cleanup and finish

#_________________________________________________________________________________________
unlink glob("$FindBin::Bin/create.sql");
print STDERR "\n";

